* ---------------------------------------------------------------------------------------------------
* industry_aggregates: Imports insurance industry aggregates
* ---------------------------------------------------------------------------------------------------

* life balance sheet
forval i=1/3 {
    import excel using "$raw/sp_insurance/additional/Life Industry Balance Sheet, Part `i'.xlsx", clear cellrange(A7) firstrow
    if `i' == 3 {
        cap drop H-L        
    }
    foreach var of varlist * {
        local tdate = `var'[1]
        local tdate = subinstr("`tdate'", " ", "_", .)
        local tdate = subinstr("`tdate'", "/", "_", .)
        local tname = "value_`tdate'"
        rename `var' `tname'
    }
    rename value_ indicator
    drop if _n < 7
    replace indicator = trim(indicator)
    drop if missing(indicator)
    save $temp/life_ind_balance_sheet_`i', replace    
}

* pc balance sheet
forval i=1/3 {
    import excel using "$raw/sp_insurance/additional/PC Industry Balance Sheet, Part `i'.xlsx", clear cellrange(A7) firstrow
    if `i' == 3 {
        cap drop H-L        
    }
    foreach var of varlist * {
        local tdate = `var'[1]
        local tdate = subinstr("`tdate'", " ", "_", .)
        local tdate = subinstr("`tdate'", "/", "_", .)
        local tname = "value_`tdate'"
        rename `var' `tname'
    }
    rename value_ indicator
    drop if _n < 7
    replace indicator = trim(indicator)
    drop if missing(indicator)
    save $temp/pc_ind_balance_sheet_`i', replace    
}

* health balance sheet
forval i=1/3 {
    import excel using "$raw/sp_insurance/additional/Health Industry Balance Sheet, Part `i'.xlsx", clear cellrange(A7) firstrow
    if `i' == 3 {
        cap drop H-L        
    }
    foreach var of varlist * {
        local tdate = `var'[1]
        local tdate = subinstr("`tdate'", " ", "_", .)
        local tdate = subinstr("`tdate'", "/", "_", .)
        local tname = "value_`tdate'"
        rename `var' `tname'
    }
    rename value_ indicator
    drop if _n < 7
    replace indicator = trim(indicator)
    drop if missing(indicator)
    save $temp/health_ind_balance_sheet_`i', replace    
}

* life income statement
forval i=1/3 {
    import excel using "$raw/sp_insurance/additional/Life Industry Income Statement, Part `i'.xlsx", clear cellrange(A7) firstrow
    if `i' == 3 {
        drop H-L
    }
    foreach var of varlist * {
        local tdate = `var'[1]
        local tdate = subinstr("`tdate'", " ", "_", .)
        local tdate = subinstr("`tdate'", "/", "_", .)
        local tname = "value_`tdate'"
        rename `var' `tname'
    }
    rename value_ indicator
    drop if _n < 7
    replace indicator = trim(indicator)
    drop if missing(indicator)
    save $temp/life_ind_income_statement_`i', replace    
}

* pc income statement
forval i=1/3 {
    import excel using "$raw/sp_insurance/additional/PC Industry Income Statement, Part `i'.xlsx", clear cellrange(A7) firstrow
    if `i' == 3 {
        cap drop H-L        
    }
    foreach var of varlist * {
        local tdate = `var'[1]
        local tdate = subinstr("`tdate'", " ", "_", .)
        local tdate = subinstr("`tdate'", "/", "_", .)
        local tname = "value_`tdate'"
        rename `var' `tname'
    }
    rename value_ indicator
    drop if _n < 7
    replace indicator = trim(indicator)
    drop if missing(indicator)
    save $temp/pc_ind_income_statement_`i', replace    
}

* health income statement
forval i=1/3 {
    import excel using "$raw/sp_insurance/additional/Health Industry Income Statement, Part `i'.xlsx", clear cellrange(A7) firstrow
    if `i' == 3 {
        cap drop H-L        
    }
    foreach var of varlist * {
        local tdate = `var'[1]
        local tdate = subinstr("`tdate'", " ", "_", .)
        local tdate = subinstr("`tdate'", "/", "_", .)
        local tname = "value_`tdate'"
        rename `var' `tname'
    }
    rename value_ indicator
    drop if _n < 7
    replace indicator = trim(indicator)
    drop if missing(indicator)
    save $temp/health_ind_income_statement_`i', replace    
}

* life reserves (stocks)
forval i = 1/3 {
    use $temp/life_ind_balance_sheet_`i', clear
    keep if indicator == "Total Policy Reserves plus Deposits"
    reshape long value_, i(indicator) j(_date) string
    split _date, parse(_)
    drop _date _date3
    rename _date1 quarter
    rename _date2 year
    destring quarter year, replace
    replace year = year + 1900 if year >= 90
    replace year = year + 2000 if year < 90
    replace quarter = quarter / 3
    gen date_q = qofd(mdy(1, 1, year)) + quarter - 1
    format %tq date_q
    rename value_ value    
    save $temp/life_aggregate_reserves_stocks_`i', replace
}

clear
forval i=1/3 {
    append using $temp/life_aggregate_reserves_stocks_`i'
}
drop indicator
rename value life_tot_reserves
order date_q
sort date_q
save "$temp/life_aggregate_reserves_stocks", replace

* pc reserves (stocks)
forval i=1/3 {
    use $temp/pc_ind_balance_sheet_`i', clear
    keep if indicator == "Total Loss & LAE Reserves"
    reshape long value_, i(indicator) j(_date) string
    split _date, parse(_)
    drop _date _date3
    rename _date1 quarter
    rename _date2 year
    destring quarter year, replace
    replace year = year + 1900 if year >= 90
    replace year = year + 2000 if year < 90
    replace quarter = quarter / 3
    gen date_q = qofd(mdy(1, 1, year)) + quarter - 1
    format %tq date_q
    rename value_ value    
    save $temp/pc_aggregate_reserves_stocks_`i', replace
}

clear
forval i=1/3 {
    append using $temp/pc_aggregate_reserves_stocks_`i'
}
drop indicator
rename value pc_tot_reserves
order date_q
sort date_q
save "$temp/pc_aggregate_reserves_stocks", replace

* health reserves (stocks)
forval i=1/2 {
    use $temp/health_ind_balance_sheet_`i', clear
    keep if indicator == "Total Policy Reserves"
    reshape long value_, i(indicator) j(_date) string
    split _date, parse(_)
    drop _date _date3
    rename _date1 quarter
    rename _date2 year
    destring quarter year, replace
    replace year = year + 1900 if year >= 90
    replace year = year + 2000 if year < 90
    replace quarter = quarter / 3
    gen date_q = qofd(mdy(1, 1, year)) + quarter - 1
    format %tq date_q
    rename value_ value    
    save $temp/health_aggregate_reserves_stocks_`i', replace
}

clear
forval i=1/2 {
    append using $temp/health_aggregate_reserves_stocks_`i'
}
drop indicator
rename value health_tot_reserves
order date_q
sort date_q
save "$temp/health_aggregate_reserves_stocks", replace

* merge reserves (stocks)
use "$temp/life_aggregate_reserves_stocks.dta", clear
mmerge date_q using "$temp/pc_aggregate_reserves_stocks"
mmerge date_q using "$temp/health_aggregate_reserves_stocks"
drop _merge
cap drop quarter year
destring life_tot_reserves pc_tot_reserves health_tot_reserves, replace
gen tot_reserves = life_tot_reserves + pc_tot_reserves + health_tot_reserves
replace tot_reserves = life_tot_reserves + pc_tot_reserves if missing(health_tot_reserves)
sort date_q
foreach var of varlist *_reserves {
    replace `var' = `var' / 1e6
}
tsset date_q
gen new_tot_reserves = tot_reserves - l.tot_reserves
replace new_tot_reserves = (tot_reserves - l4.tot_reserves) / 4 if date_q < tq(2001q1)
save "$temp/aggregate_reserves_stocks", replace

* life reserves (flows)
forval i = 1/3 {
    use $temp/life_ind_income_statement_`i', clear
    keep if indicator == "Total Inc. in Reserves"
    reshape long value_, i(indicator) j(_date) string
    split _date, parse(_)
    drop _date _date3
    rename _date1 quarter
    rename _date2 year
    destring quarter year, replace
    replace year = year + 1900 if year >= 90
    replace year = year + 2000 if year < 90
    replace quarter = quarter / 3
    gen date_q = qofd(mdy(1, 1, year)) + quarter - 1
    format %tq date_q
    rename value_ value    
    save $temp/life_aggregate_reserves_flows_`i', replace
}

clear
forval i=1/3 {
    append using $temp/life_aggregate_reserves_flows_`i'
}
drop indicator
rename value life_new_reserves
order date_q
sort date_q
save "$temp/life_aggregate_reserves_flows", replace

* health reserves (flows)
forval i = 1/2 {
    use $temp/health_ind_income_statement_`i', clear
    keep if indicator == "Increase in Reserves"
    reshape long value_, i(indicator) j(_date) string
    split _date, parse(_)
    drop _date _date3
    rename _date1 quarter
    rename _date2 year
    destring quarter year, replace
    replace year = year + 1900 if year >= 90
    replace year = year + 2000 if year < 90
    replace quarter = quarter / 3
    gen date_q = qofd(mdy(1, 1, year)) + quarter - 1
    format %tq date_q
    rename value_ value    
    save $temp/health_aggregate_reserves_flows_`i', replace
}

clear
forval i=1/2 {
    append using $temp/health_aggregate_reserves_flows_`i'
}
drop indicator
rename value health_new_reserves
order date_q
sort date_q
save "$temp/health_aggregate_reserves_flows", replace
